﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web.UI;
using System.Web.UI.HtmlControls;

namespace XYZ_Inventory
{
    public partial class InventoryReport : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                XYZDBContext dbContext = new XYZDBContext();
                ddlYears.DataTextField = "Year";
                ddlYears.DataValueField = "Year";
                ddlYears.DataSource = dbContext.FinishedProductSales.GroupBy(x => x.DateAndTime.Year).Select(y => new
                {
                    Year = y.Key
                });
                ddlYears.DataBind();
                LoadFinishedProductStatistics();
                LoadFinishedRawMaterislStatistics();
            }
        }

        private void LoadFinishedProductStatistics()
        {
            // Header rows
            HtmlTableRow row1 = new HtmlTableRow();
            HtmlTableRow row2 = new HtmlTableRow();
            HtmlTableCell cellMonth = new HtmlTableCell {RowSpan = 2, InnerText = "Month"};
            row1.Cells.Add(cellMonth);

            XYZDBContext dbContext = new XYZDBContext();
            foreach (FinishedProductInventory product in dbContext.FinishedProductInventories)
            {
                HtmlTableCell cellProduct = new HtmlTableCell {ColSpan = 2, InnerText = product.Name};
                row1.Cells.Add(cellProduct);

                HtmlTableCell cellProductSub1 = new HtmlTableCell {InnerText = "Total Finished #"};
                HtmlTableCell cellProductSub2 = new HtmlTableCell {InnerText = "Total Sales #"};
                row2.Cells.Add(cellProductSub1);
                row2.Cells.Add(cellProductSub2);
            }
            tblProductInventory.Rows.Add(row1);
            tblProductInventory.Rows.Add(row2);

            // Middle count rows
            int year = Convert.ToInt32(ddlYears.SelectedValue);
            for (int x = 1; x <= 12; x++)
            {
                HtmlTableRow row = new HtmlTableRow();
                string monthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(x);
                HtmlTableCell monthCell = new HtmlTableCell {InnerText = monthName};
                row.Cells.Add(monthCell);

                List<Statistics> finishedProducts = dbContext.FinishedProductLogs.Where(
                    y => y.DateAndTime.Year.Equals(year) && y.DateAndTime.Month.Equals(x))
                    .GroupBy(z => z.ProductId)
                    .Select(
                        a =>
                            new Statistics
                            {
                                Id = a.Key,
                                Count = a.Sum(b => b.Quantity)
                            }).ToList();

                List<Statistics> productsSales =
                    dbContext.FinishedProductSales.Where(
                        y => y.DateAndTime.Year.Equals(year) && y.DateAndTime.Month.Equals(x))
                        .GroupBy(z => z.ProductID)
                        .Select(
                            a =>
                                new Statistics
                                {
                                    Id = a.Key,
                                    Sales = a.Sum(b => b.Quantity)
                                }).ToList();

                foreach (FinishedProductInventory product in dbContext.FinishedProductInventories)
                {
                    int finished = 0;
                    int sales = 0;
                    try
                    {
                        finished =
                            finishedProducts.Single(g => g.Id == product.ProductID).Count;
                    }
                    catch
                    {
                        // ignored
                    }

                    try
                    {
                        sales =
                            productsSales.Single(g => g.Id == product.ProductID).Sales;
                    }
                    catch
                    {
                        // ignored
                    }
                    HtmlTableCell cellProductSub1 = new HtmlTableCell {InnerText = finished.ToString()};
                    HtmlTableCell cellProductSub2 = new HtmlTableCell {InnerText = sales.ToString()};
                    row.Cells.Add(cellProductSub1);
                    row.Cells.Add(cellProductSub2);
                }
                tblProductInventory.Rows.Add(row);
            }

            // Final total rows
            List<Statistics> totalFinishedProducts = dbContext.FinishedProductLogs.Where(
                y => y.DateAndTime.Year.Equals(year))
                .GroupBy(z => z.ProductId)
                .Select(
                    a =>
                        new Statistics
                        {
                            Id = a.Key,
                            Count = a.Sum(b => b.Quantity)
                        }).ToList();

            List<Statistics> totalProductsSales =
                dbContext.FinishedProductSales.Where(
                    y => y.DateAndTime.Year.Equals(year))
                    .GroupBy(z => z.ProductID)
                    .Select(
                        a =>
                            new Statistics
                            {
                                Id = a.Key,
                                Sales = a.Sum(b => b.Quantity)
                            }).ToList();

            HtmlTableRow finalRow = new HtmlTableRow();
            HtmlTableCell totalCell = new HtmlTableCell {InnerText = "Total"};
            finalRow.Cells.Add(totalCell);
            foreach (FinishedProductInventory product in dbContext.FinishedProductInventories)
            {
                int totalFinished = 0;
                int totalSales = 0;
                try
                {
                    totalFinished =
                        totalFinishedProducts.Single(g => g.Id == product.ProductID).Count;
                }
                catch
                {
                    // ignored
                }

                try
                {
                    totalSales =
                        totalProductsSales.Single(g => g.Id == product.ProductID).Sales;
                }
                catch
                {
                    // ignored
                }

                HtmlTableCell cellProductSub1 = new HtmlTableCell {InnerText = totalFinished.ToString()};
                HtmlTableCell cellProductSub2 = new HtmlTableCell {InnerText = totalSales.ToString()};
                finalRow.Cells.Add(cellProductSub1);
                finalRow.Cells.Add(cellProductSub2);

                tblProductInventory.Rows.Add(finalRow);
            }
        }

        private void LoadFinishedRawMaterislStatistics()
        {
            int year = Convert.ToInt32(ddlYears.SelectedValue);

            // Header rows
            HtmlTableRow row1 = new HtmlTableRow();
            HtmlTableRow row2 = new HtmlTableRow();
            HtmlTableCell cellMaterial = new HtmlTableCell {RowSpan = 2, InnerText = "Raw Material"};
            row1.Cells.Add(cellMaterial);

            for (int x = 1; x <= 12; x++)
            {
                string monthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(x);
                HtmlTableCell monthCell = new HtmlTableCell {ColSpan = 2, InnerText = monthName};
                row1.Cells.Add(monthCell);

                HtmlTableCell cell1 = new HtmlTableCell {InnerText = "Total import"};
                HtmlTableCell cell2 = new HtmlTableCell {InnerText = "Total sales"};
                row2.Cells.Add(cell1);
                row2.Cells.Add(cell2);
            }
            HtmlTableCell totalCell = new HtmlTableCell {ColSpan = 2, InnerText = "Total Year"};
            row1.Cells.Add(totalCell);
            HtmlTableCell totalImportCell = new HtmlTableCell {InnerText = "Total import"};
            HtmlTableCell totalSalesCell = new HtmlTableCell {InnerText = "Total sales"};
            row2.Cells.Add(totalImportCell);
            row2.Cells.Add(totalSalesCell);

            tblRawMaterialInventory.Rows.Add(row1);
            tblRawMaterialInventory.Rows.Add(row2);

            XYZDBContext dbContext = new XYZDBContext();

            List<RawMaterialOrder> orders =
                dbContext.RawMaterialOrders.Where(x => x.OrderDate.Year.Equals(year)).ToList();
            List<FinishedProductSales> sales = dbContext.FinishedProductSales.Where(
                x => x.DateAndTime.Year.Equals(year)).ToList();
            List<RawMaterialInventory> rawMaterialList = dbContext.RawMaterialInventories.ToList();
            foreach (RawMaterialInventory raw in rawMaterialList)
            {
                HtmlTableRow row = new HtmlTableRow();
                HtmlTableCell cellMaterialName = new HtmlTableCell {InnerText = raw.RawMaterialName};
                row.Cells.Add(cellMaterialName);

                int totalImport = 0;
                int totalSale = 0;

                for (int y = 1; y <= 13; y++)
                {
                    if (y <= 12)
                    {
                        // Import calculation
                        Statistics filteredOrder = new Statistics();
                        try
                        {
                            filteredOrder =
                                orders.Where(
                                    x => x.OrderDate.Month.Equals(y))
                                    .GroupBy(x => x.RawMaterialID)
                                    .Select(x => new Statistics
                                    {
                                        Id = x.Key,
                                        Count = x.Sum(a => a.Quantity)
                                    }).Single(z => z.Id == raw.RawMaterialID);
                        }
                        catch
                        {
                            filteredOrder.Id = raw.RawMaterialID;
                            filteredOrder.Count = 0;
                        }

                        // Material usage
                        Statistics material = new Statistics
                        {
                            Id = raw.RawMaterialID,
                            Sales = 0
                        };

                        List<FinishedProducts> salesFinishedProducts = new List<FinishedProducts>();
                        try
                        {
                            salesFinishedProducts = sales.Where(x => x.DateAndTime.Month.Equals(y))
                                .GroupBy(a => a.ProductID)
                                .Select(z => new FinishedProducts
                                {
                                    ProductId = z.First().ProductID,
                                    Count = z.Sum(a => a.Quantity)
                                }).ToList();
                        }
                        catch
                        {
                            // ignored
                        }

                        foreach (FinishedProducts product in salesFinishedProducts)
                        {
                            List<RawMaterialForProduct> rawMaterialsOfProduct =
                                dbContext.RawMaterialForProducts.Where(x => x.ProductID == product.ProductId).ToList();

                            foreach (RawMaterialForProduct r in rawMaterialsOfProduct)
                            {
                                if (r.RawMaterialID == material.Id)
                                    material.Sales += r.Quantity*product.Count;
                            }
                        }

                        totalImport += filteredOrder.Count;
                        totalSale += material.Sales;

                        HtmlTableCell cellProductSub1 = new HtmlTableCell {InnerText = filteredOrder.Count.ToString()};
                        HtmlTableCell cellProductSub2 = new HtmlTableCell {InnerText = material.Sales.ToString()};
                        row.Cells.Add(cellProductSub1);
                        row.Cells.Add(cellProductSub2);
                    }
                    else
                    {
                        HtmlTableCell cellProductSub1 = new HtmlTableCell {InnerText = totalImport.ToString()};
                        HtmlTableCell cellProductSub2 = new HtmlTableCell {InnerText = totalSale.ToString()};
                        row.Cells.Add(cellProductSub1);
                        row.Cells.Add(cellProductSub2);
                    }
                }
                tblRawMaterialInventory.Rows.Add(row);
            }
        }

        protected void ddlYears_OnSelectedIndexChanged(object sender, EventArgs e)
        {
            LoadFinishedProductStatistics();
            LoadFinishedRawMaterislStatistics();
        }
    }

    public class Statistics
    {
        public int Id { get; set; }
        public int Count { get; set; }
        public int Sales { get; set; }
    }
}